USE NTBSystem



/*
	GET COST HISTORY	
	*/
	
IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_getCostHistory')
DROP PROC spu_getCostHistory
GO

CREATE PROC spu_getCostHistory
@LocationID int,
@NumRow int = 30,
@FromDate datetime = '',
@ToDate datetime = ''
AS
BEGIN
	IF @FromDate = cast(@FromDate as datetime)
		SET @FromDate = '1900-01-01'
	IF @ToDate = cast(@ToDate as datetime)
		SET @ToDate = GETDATE()
		
	SELECT TOP (@NumRow)
		StandardCost,
		StartDate,
		(
			SELECT TOP 1 AVG(b.StandardCost) FROM LocationCostHistory b
			WHERE b.StartDate <= a.StartDate AND DATALENGTH(b.StandardCost) > 0
			GROUP BY StartDate
			ORDER BY b.StartDate DESC
		) AS AVGCost
		FROM LocationCostHistory a
	WHERE LocationID = @LocationID
	AND StartDate >= @FromDate AND StartDate <= @ToDate
	ORDER BY StartDate DESC
END
GO



/*
	PAGINATION
	*/

-- pagination Users

IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_pageUsers')
DROP PROC spu_pageUsers
GO

CREATE PROC spu_pageUsers
@PageIndex int,
@PageSize int = 20
AS
BEGIN
	DECLARE @start int
	SET @start = (@PageIndex - 1) * @PageSize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
	Set NOCOUNT ON
	SELECT TOP (@PageSize) * FROM 
	(
	  SELECT Users.*,
	  ROW_NUMBER() OVER (ORDER BY UserID) AS num
	  FROM Users
	) AS a
	WHERE num > @start
END
GO



-- pagination StampDutyAndRegistration

IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_pageStampDutyAndRegistration')
DROP PROC spu_pageStampDutyAndRegistration
GO

CREATE PROC spu_pageStampDutyAndRegistration
@PageIndex int,
@PageSize int = 20
AS
BEGIN
	DECLARE @start int
	SET @start = (@PageIndex - 1) * @PageSize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
	Set NOCOUNT ON
	SELECT TOP (@PageSize) * FROM 
	(
	  SELECT StampDutyAndRegistration.*,
	  ROW_NUMBER() OVER (ORDER BY ID) AS num
	  FROM StampDutyAndRegistration
	) AS a
	WHERE num > @start
END
GO



-- pagination Contacts

IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_pageContacts')
DROP PROC spu_pageContacts
GO

CREATE PROC spu_pageContacts
@PageIndex int,
@PageSize int = 20
AS
BEGIN
	DECLARE @start int
	SET @start = (@PageIndex - 1) * @PageSize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
	Set NOCOUNT ON
	SELECT TOP (@PageSize) * FROM 
	(
	  SELECT Contacts.*,
	  ROW_NUMBER() OVER (ORDER BY ContactID) AS num
	  FROM Contacts
	) AS a
	WHERE num > @start
END
GO



-- pagination Locations

IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_pageLocations')
DROP PROC spu_pageLocations
GO

CREATE PROC spu_pageLocations
@PageIndex int,
@PageSize int = 20
AS
BEGIN
	DECLARE @start int
	SET @start = (@PageIndex - 1) * @PageSize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
	Set NOCOUNT ON
	SELECT TOP (@PageSize) * FROM 
	(
	  SELECT Locations.*,
	  ROW_NUMBER() OVER (ORDER BY LocationID) AS num
	  FROM Locations
	) AS a
	WHERE num > @start
END
GO



-- pagination LandPlansLaid

IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_pageLandPlansLaid')
DROP PROC spu_pageLandPlansLaid
GO

CREATE PROC spu_pageLandPlansLaid
@PageIndex int,
@PageSize int = 20
AS
BEGIN
	DECLARE @start int
	SET @start = (@PageIndex - 1) * @PageSize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
	Set NOCOUNT ON
	SELECT TOP (@PageSize) * FROM 
	(
	  SELECT LandPlansLaid.*,
	  ROW_NUMBER() OVER (ORDER BY PlanID) AS num
	  FROM LandPlansLaid
	) AS a
	WHERE num > @start
END
GO



-- pagination Lands

IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_pageLands')
DROP PROC spu_pageLands
GO

CREATE PROC spu_pageLands
@PageIndex int,
@PageSize int = 20
AS
BEGIN
	DECLARE @start int
	SET @start = (@PageIndex - 1) * @PageSize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
	Set NOCOUNT ON
	SELECT TOP (@PageSize) * FROM 
	(
	  SELECT Lands.*,
	  ROW_NUMBER() OVER (ORDER BY LandID) AS num
	  FROM Lands
	) AS a
	WHERE num > @start
END
GO



-- pagination BuildingTypes

IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_pageBuildingTypes')
DROP PROC spu_pageBuildingTypes
GO

CREATE PROC spu_pageBuildingTypes
@PageIndex int,
@PageSize int = 20
AS
BEGIN
	DECLARE @start int
	SET @start = (@PageIndex - 1) * @PageSize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
	Set NOCOUNT ON
	SELECT TOP (@PageSize) * FROM 
	(
	  SELECT BuildingTypes.*,
	  ROW_NUMBER() OVER (ORDER BY BuildingTypeID) AS num
	  FROM BuildingTypes
	) AS a
	WHERE num > @start
END
GO



-- pagination Buildings

IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_pageBuildings')
DROP PROC spu_pageBuildings
GO

CREATE PROC spu_pageBuildings
@PageIndex int,
@PageSize int = 20
AS
BEGIN
	DECLARE @start int
	SET @start = (@PageIndex - 1) * @PageSize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
	Set NOCOUNT ON
	SELECT TOP (@PageSize) * FROM 
	(
	  SELECT Buildings.*,
	  ROW_NUMBER() OVER (ORDER BY BuildingID) AS num
	  FROM Buildings
	) AS a
	WHERE num > @start
END
GO



-- pagination Flats

IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_pageFlats')
DROP PROC spu_pageFlats
GO

CREATE PROC spu_pageFlats
@PageIndex int,
@PageSize int = 20
AS
BEGIN
	DECLARE @start int
	SET @start = (@PageIndex - 1) * @PageSize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
	Set NOCOUNT ON
	SELECT TOP (@PageSize) * FROM 
	(
	  SELECT Flats.*,
	  ROW_NUMBER() OVER (ORDER BY FlatID) AS num
	  FROM Flats
	) AS a
	WHERE num > @start
END
GO



-- pagination Sales

IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_pageSales')
DROP PROC spu_pageSales
GO

CREATE PROC spu_pageSales
@PageIndex int,
@PageSize int = 20
AS
BEGIN
	DECLARE @start int
	SET @start = (@PageIndex - 1) * @PageSize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
	Set NOCOUNT ON
	SELECT TOP (@PageSize) * FROM 
	(
	  SELECT Sales.*,
	  ROW_NUMBER() OVER (ORDER BY SaleID) AS num
	  FROM Sales
	) AS a
	WHERE num > @start
END
GO



-- pagination Payments

IF EXISTS (SELECT name FROM sys.procedures WHERE name='spu_pagePayments')
DROP PROC spu_pagePayments
GO

CREATE PROC spu_pagePayments
@PageIndex int,
@PageSize int = 20
AS
BEGIN
	DECLARE @start int
	SET @start = (@PageIndex - 1) * @PageSize
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
	Set NOCOUNT ON
	SELECT TOP (@PageSize) * FROM 
	(
	  SELECT Payments.*,
	  ROW_NUMBER() OVER (ORDER BY PayID) AS num
	  FROM Payments
	) AS a
	WHERE num > @start
END
GO